#!/usr/bin/perl -w
use strict;
use Getopt::Long;
use DBI;
use DBD::Pg;
use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use chromosomes;
use linewrap;
use uniquify;
use GO_frequency;

use db_parameters;
use ortho_species;

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Get options

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
# mandatory
my $usage = <<'USAGE';

USAGE:

    homo_go_to_species_go.pl
				--species canis
                [--verbose]
                [--help]
	
	Transfers GO annotations to the orthologues of another species from
		human
		
USAGE

# optional parameters
my $verbose;
my $species;
my $help;
GetOptions(
			'verbose'			=> \$verbose,
			'species=s'			=> \$species,
			'help'				=> \$help);


die $usage if ($help);
die "Error:\nPlease supply the recipient species.\n".$usage
		unless ($species);


		
#
#	Connect to genome database
#
my $dbh = connect_to_panda();
use get_psql_time;
my $start_time = get_psql_time($dbh);
$dbh->{RaiseError} = 0;

	print STDERR ("\t\tRetrieving all GO ID counts per for homo...\n");

	$dbh->do(<<"PL/SQLCMD") or die DBI::errstr;
    CREATE TEMP TABLE t_consensus_go_from_human1 (ortholog_id INTEGER, GO_id INTEGER, frequency INTEGER);
    INSERT
        INTO t_consensus_go_from_human1(ortholog_id, GO_id, frequency)
        SELECT 
                ortholog_id,
                go_id, 
                COUNT(*)
            FROM
                orthologs.ortholog_sets 
                JOIN taxon.gene_go_matches USING (gene_id, species) 
            WHERE 
                protocol_id = $curr_protocol_id AND 
                species = 'homo' 
            GROUP BY 
                go_id, ortholog_id;
PL/SQLCMD

	print STDERR ("\t\tCompare with numbers of paralogs...\n");


	$dbh->do(<<"PL/SQLCMD") or die DBI::errstr;
    CREATE TEMP TABLE t_consensus_go_from_human2 (ortholog_id INTEGER, GO_id INTEGER, 
             frequency INTEGER, paralogs INTEGER, percentage REAL);
    INSERT
        INTO t_consensus_go_from_human2 (ortholog_id, GO_id, frequency, 
                                         paralogs, percentage)
        SELECT
                ortholog_id,
                go_id,
                frequency,
                paralogs,
                least((frequency * 1.0) / a.paralogs, 1.0)
            FROM 
                t_consensus_go_from_human1
                JOIN 
                (
                    SELECT 
                            ortholog_id,
                            greatest(COUNT(*), 1) AS paralogs
                        FROM 
                            orthologs.ortholog_sets 
                        WHERE 
                            protocol_id = $curr_protocol_id AND 
                            species = 'homo' AND 
                            ortholog_type ~ 'to'
                        GROUP BY ortholog_id
                ) AS a USING (ortholog_id);
PL/SQLCMD

	print STDERR ("\t\tCreating GO ids per $species gene from human orthologues...\n");
#	$dbh->do(<<"PL/SQLCMD");
#	DROP TABLE  t_other_go_terms_per_gene;
#PL/SQLCMD
	$dbh->do(<<"PL/SQLCMD") or die DBI::errstr;
    CREATE TEMP TABLE  t_other_go_terms_per_gene (gene_id TEXT, GO_id INTEGER);
    INSERT
        INTO t_other_go_terms_per_gene (gene_id, GO_id)
        SELECT
                gene_id, 
                go.GO_id
            FROM 
                orthologs.ortholog_sets os JOIN 
                t_consensus_go_from_human2 go USING (ortholog_id) 
            WHERE 
                protocol_id = $curr_protocol_id AND 
                species = '$species' AND 
                percentage > 0.5;
PL/SQLCMD



	print STDERR ("\t\tCreating GO ids for orphans from closest human homologue with ds < 1.5...\n");
	$dbh->do(<<"PL/SQLCMD") or die DBI::errstr;
    INSERT
        INTO t_other_go_terms_per_gene (gene_id, GO_id)
        SELECT 
                co.gene_id,
                go.go_id
            FROM 
                taxon.gene_go_matches go, 
                orthologs.orphans JOIN 
                orthologs.closest_other_species co USING (gene_id, species, protocol_id) 
            WHERE 
                protocol_id = $curr_protocol_id AND 
                co.species = '$species' AND 
                go.gene_id = co.other_species_gene_id AND
                ds < 1.5;
PL/SQLCMD

	print STDERR ("\t\tPut back into taxon.gene_go_matches...\n");
	$dbh->do(<<"PL/SQLCMD");
    DELETE FROM taxon.gene_go_matches WHERE species = '$species';
PL/SQLCMD
	$dbh->do(<<"PL/SQLCMD");
    VACUUM ANALYSE taxon.gene_go_matches;
PL/SQLCMD
	$dbh->do(<<"PL/SQLCMD") or die DBI::errstr;
    INSERT
        INTO taxon.gene_go_matches (gene_id, GO_id, species)
        SELECT 
                gene_id,
                go_id,
                '$species'
            FROM 
                t_other_go_terms_per_gene;
PL/SQLCMD








# write out sequences with mulitple sizes

log_pipeline_stage($dbh, $curr_protocol_id, 43, 'table homo_go_to_species', $start_time);
$dbh->disconnect();

